CREATE TRIGGER checkNumOfDrowsinessDrugs
ON Prescribes FOR INSERT,UPDATE AS

BEGIN
	DECLARE @doctorId INT
	DECLARE @patientId INT
	DECLARE @date DATETIME
	DECLARE @updatedCount INT

    SET @updatedCount = (SELECT COUNT(*) FROM INSERTED)

    IF (@updatedCount = 1)
    BEGIN

	    SET @doctorId = (SELECT doctorId FROM INSERTED)
	    SET @patientId = (SELECT patientId FROM INSERTED)
	    SET @date = (SELECT date FROM INSERTED)

		/*No prescription should have more than two drugs that cause drowsiness*/
		IF ((SELECT COUNT(*)
			 FROM	Prescribes p, (SELECT tradeName, companyName FROM HasSideEffect WHERE name='Drowsiness') drowsy_drugs
			 WHERE	p.doctorId = @doctorId 
					AND p.patientId = @patientId 
					AND p.date = @date
					AND p.tradeName = drowsy_drugs.tradeName
					AND p.companyName = drowsy_drugs.companyName
			 ) > 2)
			BEGIN
				PRINT 'There can only be 2 or less drowiness drugs in one prescription.'
				ROLLBACK
			END
    END
END
